connect by NoCycle
再訪問を防いだ階層問い合わせ
cycleというのは英語で、「ひと巡り、一巡、周期」という意味です。
connect by NoCycleを使うと、経路上で訪問済であるノードへの再訪問を防いだ階層問い合わせを行うことができます。
connect by NoCycleはデータ構造が有向グラフや無向グラフである場合によく使われます。
create table NoCycleSample(ID primary key,NextID) as
select 1, 2 from dual union all
select 2, 3 from dual union all
select 3,null from dual union all
select 50, 51 from dual union all
select 51, 52 from dual union all
select 52, 50 from dual; 木の根となる条件を、ID = 1
親子条件を、親のNextID = 子のID
として階層問い合わせを行います。
-- 閉路のない階層問い合わせ
select ID,NextID,Level,
sys_connect_by_path(to_char(ID),',') as Path
from NoCycleSample
start with ID = 1
connect by prior NextID = ID;
出力結果
ID NextID Level Path
-- ------ ----- ------
1 2 1 ,1
2 3 2 ,1,2
3 null 3 ,1,2,3 次は、木の根となる条件を、ID = 50
親子条件を、親のNextID = 子のID
として階層問い合わせを行います。
-- 閉路のある階層問い合わせ1
select ID,NextID,Level,
sys_connect_by_path(to_char(ID),',') as Path
from NoCycleSample
start with ID = 50
connect by prior NextID = ID;
出力結果 ERROR: ORA-01436: ユーザー・データでCONNECT BYのループが発生しました start with ID = 50によって、ID=50の行を根とした階層問い合わせが行われますが、
ID=50の行の子供が、ID=51の行。
ID=51の行の子供が、ID=52の行。
ID=52の行の子供が、ID=50の行。
といったデータになっていて、経路上で訪問済であるノードへの再訪問が行われるため、
ORA-01436が発生してしまいます。
このように閉路のあるデータ構造の時には、connect by NoCycleを使うと、親子関係があるけど経路上で訪問済であるノードへの再訪問を防いだ階層問い合わせを行うことができます。
connect by NoCycleは、connect_by_IsCycle疑似列と併用できます。
connect_by_IsCycle疑似列は、経路上で訪問済であるノードを子供に持てば1、そうでなければ0となります。
-- 閉路のある階層問い合わせ2
select ID,NextID,Level,
sys_connect_by_path(to_char(ID),',') as Path,
connect_by_IsCycle as IsCycle
from NoCycleSample
start with ID = 50
connect by NoCycle prior NextID = ID;
出力結果
ID NextID Level Path IsCycle
-- ------ ----- --------- -------
50 51 1 ,50 0
51 52 2 ,50,51 0
52 50 3 ,50,51,52 1 connect by NoCycleとconnect_by_IsCycle疑似列のイメージは、下記となります。
赤色のバツ印で、親子関係があるけど経路上で訪問済であるノードへの再訪問を防いでます。
connect_by_IsCycle疑似列
訪問済ノードを子供に持つかを表す
移動先を複数持てる有向グラフを対象とした階層問い合わせで、connect_by_IsCycle疑似列を使ってみます。
create table IsCycleSample(
ID primary key,NextID1,NextID2) as
select 1, 2, 4 from dual union all
select 2, 3,null from dual union all
select 3, 4,null from dual union all
select 4, 5,null from dual union all
select 5, 2,null from dual;木の根となる条件を、ID = 1
親子条件を、親のNextID1 = 子のID または 親のNextID2 = 子のID
として階層問い合わせを行い、
(同じノードへ再訪問しない)各ノードまでの経路を列挙します。
-- connect_by_IsCycle擬似列の使用例
select ID,Level,sys_connect_by_path(to_char(ID),',') as Path,
connect_by_IsCycle as IsCycle
from IsCycleSample
start with ID = 1
connect by NoCycle ID in(prior NextID1,prior NextID2);
出力結果
ID Level Path IsCycle
-- ----- ---------- -------
1 1 ,1 0
2 2 ,1,2 0
3 3 ,1,2,3 0
4 4 ,1,2,3,4 0
5 5 ,1,2,3,4,5 1
4 2 ,1,4 0
5 3 ,1,4,5 0
2 4 ,1,4,5,2 0
3 5 ,1,4,5,2,3 1 上記の結果から、connect_by_IsCycle疑似列は、経路上で訪問済であるノードを子供に持てば1、そうでなければ0となることが確認できます。
SQLのイメージは下記となります。有向グラフをイメージしてます。
